﻿CREATE PROCEDURE [dbo].[GetRecentRentProperty]
@AddressKeyword nvarchar(50),
@PropertyType nvarchar(50)= 'LAIN-LAIN',
@Daerah nvarchar(50) = '',
@Limit int,
@UserID bigint=0
AS
BEGIN

	SET NOCOUNT ON;
-- INSERT QUERY INFO INTO Mobile_UserQueryTransact
INSERT INTO Mobile_UserQueryTransact(UserID,AddressKeyword, JenisHartanah, TransactionType)
VALUES (@UserID,@AddressKeyword + ',' + @Daerah,@PropertyType,0)
	
IF @PropertyType='LAIN-LAIN'

	select top (@Limit) 
	LandType,
	PropertyType,
	LTRIM(RIGHT(Alamat,LEN(Alamat)-CHARINDEX(',',Alamat))) as Alamat ,Kawasan ,Daerah,
	ISNULL(LTRIM(RIGHT(Alamat,LEN(Alamat)-CHARINDEX(',',Alamat))),'') + ',' + ISNULL(Kawasan,'') + ',' 
	+ ISNULL(Project,'') + ',' + ISNULL(Daerah,'') as Address,
	Price as Price,
	TransactionDate as TransactionDate, 
	Land  as Land,
	BuildUp  as BuildUp,
	Bedroom as Bedroom
	from aw_recentrentsale
	where (LandType = @PropertyType or LandType IS NULL) and
	(Alamat like '%' + @AddressKeyword+ '%' or Kawasan like '%'+@AddressKeyword+'%' or Daerah like '%'+@AddressKeyword+'%' or Project like '%' + @AddressKeyword + '%') and
	(@Daerah = '' or Daerah = @Daerah) and LSK = 1 and TransactType = 'RENT'
	order by TransactionDate desc;

ELSE

	select top (@Limit) 
	LandType,
	PropertyType,
	LTRIM(RIGHT(Alamat,LEN(Alamat)-CHARINDEX(',',Alamat))) as Alamat ,Kawasan ,Daerah,
	ISNULL(LTRIM(RIGHT(Alamat,LEN(Alamat)-CHARINDEX(',',Alamat))),'') + ',' + ISNULL(Kawasan,'') + ',' 
	+ ISNULL(Project,'') + ',' + ISNULL(Daerah,'') as Address,
	Price as Price,
	TransactionDate as TransactionDate, 
	Land  as Land,
	BuildUp  as BuildUp,
	Bedroom as Bedroom
	from aw_recentrentsale
	where (LandType = @PropertyType) and
	(Alamat like '%' + @AddressKeyword+ '%' or Kawasan like '%'+@AddressKeyword+'%' or Daerah like '%'+@AddressKeyword+'%' or Project like '%' + @AddressKeyword + '%') and
	(@Daerah = '' or Daerah = @Daerah) and LSK = 1 and TransactType = 'RENT'
	order by TransactionDate desc;

END